# 个人中心dao层
from dao import db


# 数据库中修改昵称
def dao_personal_changeNickName(uuid, newNickName):
    sql_1 = "update user set nickName = '%s', userName = '%s' where uuid = %d" % (newNickName, newNickName, uuid)
    try:
        db.session.execute(sql_1)
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        return False
    else:
        sql_2 = "update communityArticle set userName = '%s' where uuid = %d" % (newNickName, uuid)
        try:
            db.session.execute(sql_2)
            db.session.commit()
        except Exception as e:
            db.session.rollback()
        return True


# 数据库中修改头像url
def dao_personal_changeAvatar(uuid, newAvatar):
    sql = "update user set avatar = '%s' where uuid = %d" % (newAvatar, uuid)
    try:
        db.session.execute(sql)
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        return False
    else:
        return True


# 数据库中查询提交记录
def dao_personal_submitLog(uuid):
    # 数据库中查询提交记录，弃用

    return False


# 数据库更新日历图
def dao_personal_uploadChart(uuid, year):
    # 数据库更新日历图，弃用

    return False


# 数据库中删除文章
def dao_personal_deleteArticle(articleId):
    sql = "delete from communityArticle where articleId = %d" % articleId
    try:
        db.session.execute(sql)
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        return False
    else:
        sql = "delete from articleLike where articleId = %d" % articleId
        try:
            db.session.execute(sql)
            db.session.commit()
        except Exception as e:
            db.session.rollback()

        sql = "select commitId from communityComments where articleId = %d" % articleId
        data = db.session.execute(sql).fetchall()
        for temp in data:
            field_li = ['commitId']
            t_result = dict(zip(field_li, temp))

            sql = "delete from commentsLike where commitId = %d" % t_result['commitId']
            try:
                db.session.execute(sql)
                db.session.commit()
            except Exception as e:
                db.session.rollback()

            sql = "select replyId from communityReplys where commitId = %d" % t_result['commitId']
            data_1 = db.session.execute(sql).fetchall()
            for t in data_1:
                field_li_1 = ['replyId']
                t_result_1 = dict(zip(field_li, temp))

                sql = "delete from replysLike where replyId = %d" % t_result_1['replyId']
                try:
                    db.session.execute(sql)
                    db.session.commit()
                except Exception as e:
                    db.session.rollback()

            sql = "delete from communityReplys where commitId = %d" % t_result['commitId']
            try:
                db.session.execute(sql)
                db.session.commit()
            except Exception as e:
                db.session.rollback()

        sql = "delete from communityComments where articleId = %d" % articleId
        try:
            db.session.execute(sql)
            db.session.commit()
        except Exception as e:
            db.session.rollback()

        return True


# 查询用户名
def dao_personal_searchUserName(uuid):
    sql = "select userName from user where uuid = %d" % uuid
    data = db.session.execute(sql).fetchone()
    if data is not None:
        field_li = ['userName']
        userName = dict(zip(field_li, data))
        return userName['userName']
    else:
        return '用户'


# 数据库保存文章
def dao_personal_submitArticle(content, uuid, title, coverImage):
    sql_1 = "select max(articleId) from communityArticle"
    data_1 = db.session.execute(sql_1).fetchone()
    if data_1 is not None:
        field_li_1 = ['articleId']
        aId = dict(zip(field_li_1, data_1))
        articleId = {'articleId': aId['articleId'] + 1}
    else:
        articleId = {'articleId': 100000}

    userName = dao_personal_searchUserName(uuid)
    defaultCover = "http://43.140.221.237:9999/imgs/articalCover/default.jpg"

    sql_2 = "insert into communityArticle (articleId, uuid, title, coverUrl, articleUploadDate, articleContent, " \
            "descript, userName, coverUrl) values (%d, %d, '%s', '%s', now(), '%s', '%s', '%s', '%s')" \
            % (articleId['articleId'], uuid, title, coverImage, content, title, userName, defaultCover)
    try:
        db.session.execute(sql_2)
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        return False
    else:
        return True


# 数据库修改文章
def dao_personal_changeArticle(content, uuid, articleId, title, coverImage):
    sql = "update communityArticle set  title = '%s', coverUrl = '%s', articleContent = '%s', descript = '%s'" \
          "where  articleId = %d and uuid = %d" % (title, coverImage, content, title, articleId, uuid)
    try:
        db.session.execute(sql)
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        return False
    else:
        return True


# 按照要求返回我发布的文章
def dao_personal_myArticle(pagination, amount, rule, uuid):
    # 筛选规则，1 代表按点赞排序，2 代表按时间排序，3 代表按浏览量排序
    if pagination == 1:
        start_limit = 0
    else:
        start_limit = amount * (pagination - 1)

    if rule == 1:
        sql_1 = "select articleId, uuid, articleUploadDate, browse, title, descript, coverUrl, likes, commits, " \
                "userName from communityArticle where uuid = %d order by likes desc limit %d, %d" \
                % (uuid, start_limit, amount)
        data_1 = db.session.execute(sql_1).fetchall()
        if data_1 is None:
            return False
        else:
            result = []
            field_li_1 = ['articleId', 'uuid', 'uploadDate', 'browse', 'title', 'description', 'coverUrl', 'like',
                          'commit', 'userName']
            for data_1_i in data_1:
                temp_1 = dict(zip(field_li_1, data_1_i))
                temp_1['articleId'] = str(temp_1['articleId'])
                temp_1['uuid'] = str(temp_1['uuid'])
                temp_1['uploadDate'] = str(temp_1['uploadDate'])
                result.append(temp_1)
            return result
    elif rule == 2:
        sql_2 = "select articleId, uuid, articleUploadDate, browse, title, descript, coverUrl, likes, commits, " \
                "userName from communityArticle where uuid = %d order by articleUploadDate limit %d, %d" \
                % (uuid, start_limit, amount)
        data_2 = db.session.execute(sql_2).fetchall()
        if data_2 is None:
            return False
        else:
            result = []
            field_li_2 = ['articleId', 'uuid', 'uploadDate', 'browse', 'title', 'description', 'coverUrl', 'like',
                          'commit', 'userName']
            for data_2_i in data_2:
                temp_2 = dict(zip(field_li_2, data_2_i))
                temp_2['articleId'] = str(temp_2['articleId'])
                temp_2['uuid'] = str(temp_2['uuid'])
                temp_2['uploadDate'] = str(temp_2['uploadDate'])
                result.append(temp_2)
            return result
    elif rule == 3:
        sql_3 = "select articleId, uuid, articleUploadDate, browse, title, descript, coverUrl, likes, commits, " \
                "userName from communityArticle where uuid = %d order by browse desc limit %d, %d" \
                % (uuid, start_limit, amount)
        data_3 = db.session.execute(sql_3).fetchall()
        if data_3 is None:
            return False
        else:
            result = []
            field_li_3 = ['articleId', 'uuid', 'uploadDate', 'browse', 'title', 'description', 'coverUrl', 'like',
                          'commit', 'userName']
            for data_3_i in data_3:
                temp_3 = dict(zip(field_li_3, data_3_i))
                temp_3['articleId'] = str(temp_3['articleId'])
                temp_3['uuid'] = str(temp_3['uuid'])
                temp_3['uploadDate'] = str(temp_3['uploadDate'])
                result.append(temp_3)
            return result
    else:
        return False
